<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" import="support.*, java.util.*, java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
	<title>Graduate School Application - Account Creation</title>
	<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
	<link rel="stylesheet" type="text/css" href="resource/styleCSS.css" />
</head>
<body>
 	<% 	
    	String uname = request.getParameter("uname");
 		String pword = request.getParameter("pword");
 		String rpword = request.getParameter("rpword");
 		String email = request.getParameter("email");
 	
 		String error_url = "registerJSP.jsp?error=";
 		boolean isNULL = false;
 		boolean debug = true;
 		boolean hasError = false;
 		
 		if ( uname == null || pword == null || rpword == null 
 				|| email == null) {
 			isNULL = true;
 		}
 		else {
 			// JH: check if password provided is empty or mismatched
 			if ( pword.contentEquals("") || pword.contentEquals("") || 
 					!pword.contentEquals(rpword) ) {
 				error_url = error_url.concat("password");
 				response.sendRedirect(error_url);
 				hasError = true;
 			}
 		}
    %>
    <p class="sansserif">
	<div class="container">
	<div class="center_div">
		<p><b>Applicant Registration</b></p>
		<ul class="none">
			<% 
				if (isNULL == true) {
					out.println("<li>Missing Registration Information</li>");
				}
				else {
			%>	
			 <%-- Open Connection Code--%>
			<% 
				String db_url = "jdbc:postgresql://localhost:5432/CSE135_SP11";
				String user ="postgres";
				String passwd ="postgres";
				Connection conn;
				PreparedStatement pstmt;
				ResultSet rset;
							
				try {
					Class.forName("org.postgresql.Driver");	
				}
				catch (java.lang.ClassNotFoundException e){
					System.err.println("ClassNotFoundException: " + e.getMessage());
				}
							
				try {
					conn = DriverManager.getConnection(db_url, user, passwd);
					
					conn.setAutoCommit(false);
			%>	
			<%-- Query Statement --%>
			<% 		
					int  uid = 0;
					
					if (!hasError) {
						// JH: check if user has already been register: i.e. email
						pstmt = conn.prepareStatement(
							"SELECT id " + 
							"FROM Users " + 
							"WHERE email = ?"); 
						
						pstmt.setString(1, email);
									
						rset = pstmt.executeQuery();
						
						while (	rset.next() ) {
							 uid = rset.getInt("id");
						}
			 			
						// JH: email already exist
						if ( uid > 0) {
							if (debug) {
								System.err.println("[DEBUG] The Email, " + email + 
										"(" + uid + ") already exists");
							}	
							
							error_url = error_url.concat("email");
			 				response.sendRedirect(error_url);
			 				hasError = true;
						}
					}
					
		 			// JH: check if username has been taken already
		 			if (!hasError) {
			 			pstmt = conn.prepareStatement(
							"SELECT id " + 
							"FROM Users " + 
							"WHERE user_name = ?"); 
						
						pstmt.setString(1, uname);
									
						rset = pstmt.executeQuery();
						
						while (	rset.next() ) {
							uid = rset.getInt("id");
						}
			 			
						// JH: username has already been taken
						if (uid > 0) {
							if (debug) {
								System.err.println("[DEBUG] The Username, " + uname + 
										"(" + uid + ") already exists");
							}	
							
							error_url = error_url.concat(uname);
			 				response.sendRedirect(error_url);
			 				hasError = true;
						}
		 			}
					
					if (!hasError) {
			 			// JH: insert user to user table
			 			pstmt = conn.prepareStatement(
								"INSERT INTO Users (user_name, password, email) " + 
								"VALUES (?, md5(?), ?)");
						
			 			pstmt.setString(1, uname);
			 			pstmt.setString(2, pword);
			 			pstmt.setString(3, email);
			 			
			 			pstmt.executeUpdate();
			 			
			 			// JH: insert user to user role table
			 			pstmt = conn.prepareStatement(
								"INSERT INTO User_Roles (user_name, role) " + 
								"VALUES (?, ?)");
						
			 			pstmt.setString(1, uname);
			 			pstmt.setString(2, "student");
			 			
			 			pstmt.executeUpdate();
			 			
						out.println("<li>Registration Complete!!!</li>");
					}
					
					conn.commit();
					conn.setAutoCommit(true);
			%>
			<%
					}
					catch(SQLException e){
						System.err.println("SQLException: " + e.getMessage());
					}
				}
			%>
		</ul>
		<form method="GET" action="index.html">
			<input type="submit" value="Back to Login"/>
		</form>
	</div>
	</div>	
</body>
</html>